CREATE TABLE [dbo].[KHACH_HANG_BOOKING]
(
[SO_BOOKING] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HO_TEN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TEN_CTY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIA_CHI] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MS_THUE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GIA] [int] NOT NULL,
[TG_DAT] [datetime] NOT NULL,
[MATV] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MA_TOUR] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[THANH_TOAN] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DIEN_THOAI_1] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DIEN_THOAI_2] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[utr_CapNhatLoaiThanhVien] ON [dbo].[KHACH_HANG_BOOKING]
FOR INSERT, UPDATE
AS
BEGIN 
	DECLARE @X INT
	DECLARE @MATV CHAR(10)

	SELECT  @X = SUM(KH.GIA), @MATV = KH.MATV
	FROM KHACH_HANG_BOOKING KH
	WHERE KH.MATV IS NOT NULL AND KH.THANH_TOAN = 'ROI'
	GROUP BY KH.MATV

	IF (@X <= 5000000)
	BEGIN
		UPDATE THANH_VIEN
		SET LOAITV = 'STANDARD'
		WHERE THANH_VIEN.MATV = @MATV
	END
	ELSE
	IF (@X BETWEEN 5000000 AND 20000000)
	BEGIN
		UPDATE THANH_VIEN
		SET LOAITV = 'SILVER'
		WHERE THANH_VIEN.MATV = @MATV
	END
	ELSE
	IF (@X BETWEEN 20000000 AND 50000000)
	BEGIN
		UPDATE THANH_VIEN
		SET LOAITV = 'GOLD'
		WHERE THANH_VIEN.MATV = @MATV
	END
	ELSE
	IF (@X >= 50000000)
	BEGIN
		UPDATE THANH_VIEN
		SET LOAITV = 'PLATINUM'
		WHERE THANH_VIEN.MATV = @MATV
	END
END

GO
ALTER TABLE [dbo].[KHACH_HANG_BOOKING] ADD CONSTRAINT [CK__KHACH_HAN__THANH__117F9D94] CHECK (([THANH_TOAN]='CHUA' OR [THANH_TOAN]='ROI'))
GO
ALTER TABLE [dbo].[KHACH_HANG_BOOKING] ADD CONSTRAINT [CK__KHACH_HANG___GIA__108B795B] CHECK (([GIA]>=(0)))
GO
ALTER TABLE [dbo].[KHACH_HANG_BOOKING] ADD CONSTRAINT [PK__KHACH_HA__D526A64E4F9E5779] PRIMARY KEY CLUSTERED  ([SO_BOOKING]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_KHACHHANGBOOKING_MATOUR] ON [dbo].[KHACH_HANG_BOOKING] ([MA_TOUR]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_KHACHHANGBOOKING_MATV] ON [dbo].[KHACH_HANG_BOOKING] ([MATV]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KHACH_HANG_BOOKING] ADD CONSTRAINT [FK_KHBOOKING_TOUR] FOREIGN KEY ([MA_TOUR]) REFERENCES [dbo].[TOUR] ([MA_TOUR])
GO
ALTER TABLE [dbo].[KHACH_HANG_BOOKING] ADD CONSTRAINT [FK_KHBOOKING_THANHVIEN] FOREIGN KEY ([MATV]) REFERENCES [dbo].[THANH_VIEN] ([MATV])
GO
